
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 01/16/2013 10:06:47
-- Generated from EDMX file: D:\dev\UnOfficial\TripAssistant\Sources\TripAssistant\DAL\TripAssistantDM.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [TripAssistant];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------


-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------


-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'Trips'
CREATE TABLE [dbo].[Trips] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [StartDate] datetime  NOT NULL,
    [EndDate] datetime  NOT NULL,
    [Name] nvarchar(max)  NOT NULL,
    [AutoStart] bit  NOT NULL,
    [Country_Id] int  NOT NULL
);
GO

-- Creating table 'Countries'
CREATE TABLE [dbo].[Countries] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'PlacesToVisit'
CREATE TABLE [dbo].[PlacesToVisit] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [TripId] int  NOT NULL,
    [Name] nvarchar(max)  NOT NULL,
    [Description] nvarchar(max)  NOT NULL,
    [Picture] varbinary(max)  NOT NULL,
    [Details] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'Users'
CREATE TABLE [dbo].[Users] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [FirstName] nvarchar(max)  NOT NULL,
    [LastName] nvarchar(max)  NOT NULL,
    [Email] nvarchar(max)  NOT NULL,
    [Password] nvarchar(max)  NOT NULL,
    [Avatar] varbinary(max)  NULL
);
GO

-- Creating table 'UserRoles'
CREATE TABLE [dbo].[UserRoles] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [UserId] int  NOT NULL,
    [RoleId] int  NOT NULL
);
GO

-- Creating table 'TripRoles'
CREATE TABLE [dbo].[TripRoles] (
    [Id] int IDENTITY(1,1) NOT NULL
);
GO

-- Creating table 'UserTripRoles'
CREATE TABLE [dbo].[UserTripRoles] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [UserId] int  NOT NULL,
    [TripRoleId] int  NOT NULL,
    [TripId] int  NOT NULL
);
GO

-- Creating table 'TripWatchers'
CREATE TABLE [dbo].[TripWatchers] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [TripId] int  NOT NULL,
    [UserId] int  NOT NULL
);
GO

-- Creating table 'Roles'
CREATE TABLE [dbo].[Roles] (
    [Id] int IDENTITY(1,1) NOT NULL
);
GO

-- Creating table 'Orders'
CREATE TABLE [dbo].[Orders] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [TripId] int  NOT NULL,
    [Status] nvarchar(max)  NOT NULL,
    [Rating] float  NOT NULL,
    [Approved] bit  NULL,
    [Name] nvarchar(max)  NOT NULL,
    [Details] nvarchar(max)  NOT NULL,
    [User_Id] int  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [Id] in table 'Trips'
ALTER TABLE [dbo].[Trips]
ADD CONSTRAINT [PK_Trips]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Countries'
ALTER TABLE [dbo].[Countries]
ADD CONSTRAINT [PK_Countries]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'PlacesToVisit'
ALTER TABLE [dbo].[PlacesToVisit]
ADD CONSTRAINT [PK_PlacesToVisit]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Users'
ALTER TABLE [dbo].[Users]
ADD CONSTRAINT [PK_Users]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'UserRoles'
ALTER TABLE [dbo].[UserRoles]
ADD CONSTRAINT [PK_UserRoles]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'TripRoles'
ALTER TABLE [dbo].[TripRoles]
ADD CONSTRAINT [PK_TripRoles]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'UserTripRoles'
ALTER TABLE [dbo].[UserTripRoles]
ADD CONSTRAINT [PK_UserTripRoles]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'TripWatchers'
ALTER TABLE [dbo].[TripWatchers]
ADD CONSTRAINT [PK_TripWatchers]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Roles'
ALTER TABLE [dbo].[Roles]
ADD CONSTRAINT [PK_Roles]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Orders'
ALTER TABLE [dbo].[Orders]
ADD CONSTRAINT [PK_Orders]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [Country_Id] in table 'Trips'
ALTER TABLE [dbo].[Trips]
ADD CONSTRAINT [FK_CountryTrip]
    FOREIGN KEY ([Country_Id])
    REFERENCES [dbo].[Countries]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_CountryTrip'
CREATE INDEX [IX_FK_CountryTrip]
ON [dbo].[Trips]
    ([Country_Id]);
GO

-- Creating foreign key on [TripId] in table 'PlacesToVisit'
ALTER TABLE [dbo].[PlacesToVisit]
ADD CONSTRAINT [FK_TripPlaceToVisit]
    FOREIGN KEY ([TripId])
    REFERENCES [dbo].[Trips]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_TripPlaceToVisit'
CREATE INDEX [IX_FK_TripPlaceToVisit]
ON [dbo].[PlacesToVisit]
    ([TripId]);
GO

-- Creating foreign key on [UserId] in table 'UserRoles'
ALTER TABLE [dbo].[UserRoles]
ADD CONSTRAINT [FK_UserUserRoles]
    FOREIGN KEY ([UserId])
    REFERENCES [dbo].[Users]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_UserUserRoles'
CREATE INDEX [IX_FK_UserUserRoles]
ON [dbo].[UserRoles]
    ([UserId]);
GO

-- Creating foreign key on [UserId] in table 'UserTripRoles'
ALTER TABLE [dbo].[UserTripRoles]
ADD CONSTRAINT [FK_UserUserTripRole]
    FOREIGN KEY ([UserId])
    REFERENCES [dbo].[Users]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_UserUserTripRole'
CREATE INDEX [IX_FK_UserUserTripRole]
ON [dbo].[UserTripRoles]
    ([UserId]);
GO

-- Creating foreign key on [TripRoleId] in table 'UserTripRoles'
ALTER TABLE [dbo].[UserTripRoles]
ADD CONSTRAINT [FK_TripRoleUserTripRole]
    FOREIGN KEY ([TripRoleId])
    REFERENCES [dbo].[TripRoles]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_TripRoleUserTripRole'
CREATE INDEX [IX_FK_TripRoleUserTripRole]
ON [dbo].[UserTripRoles]
    ([TripRoleId]);
GO

-- Creating foreign key on [TripId] in table 'UserTripRoles'
ALTER TABLE [dbo].[UserTripRoles]
ADD CONSTRAINT [FK_TripUserTripRole]
    FOREIGN KEY ([TripId])
    REFERENCES [dbo].[Trips]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_TripUserTripRole'
CREATE INDEX [IX_FK_TripUserTripRole]
ON [dbo].[UserTripRoles]
    ([TripId]);
GO

-- Creating foreign key on [TripId] in table 'TripWatchers'
ALTER TABLE [dbo].[TripWatchers]
ADD CONSTRAINT [FK_TripTripWatchers]
    FOREIGN KEY ([TripId])
    REFERENCES [dbo].[Trips]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_TripTripWatchers'
CREATE INDEX [IX_FK_TripTripWatchers]
ON [dbo].[TripWatchers]
    ([TripId]);
GO

-- Creating foreign key on [UserId] in table 'TripWatchers'
ALTER TABLE [dbo].[TripWatchers]
ADD CONSTRAINT [FK_UserTripWatchers]
    FOREIGN KEY ([UserId])
    REFERENCES [dbo].[Users]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_UserTripWatchers'
CREATE INDEX [IX_FK_UserTripWatchers]
ON [dbo].[TripWatchers]
    ([UserId]);
GO

-- Creating foreign key on [RoleId] in table 'UserRoles'
ALTER TABLE [dbo].[UserRoles]
ADD CONSTRAINT [FK_RoleUserRoles]
    FOREIGN KEY ([RoleId])
    REFERENCES [dbo].[Roles]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_RoleUserRoles'
CREATE INDEX [IX_FK_RoleUserRoles]
ON [dbo].[UserRoles]
    ([RoleId]);
GO

-- Creating foreign key on [TripId] in table 'Orders'
ALTER TABLE [dbo].[Orders]
ADD CONSTRAINT [FK_TripOrder]
    FOREIGN KEY ([TripId])
    REFERENCES [dbo].[Trips]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_TripOrder'
CREATE INDEX [IX_FK_TripOrder]
ON [dbo].[Orders]
    ([TripId]);
GO

-- Creating foreign key on [User_Id] in table 'Orders'
ALTER TABLE [dbo].[Orders]
ADD CONSTRAINT [FK_UserOrder]
    FOREIGN KEY ([User_Id])
    REFERENCES [dbo].[Users]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_UserOrder'
CREATE INDEX [IX_FK_UserOrder]
ON [dbo].[Orders]
    ([User_Id]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------